# coding:utf-8

import os
import set_env_path     # 请勿删除

import re
import time
import json

import requests
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, JSON, create_engine
from sqlalchemy.orm import sessionmaker

from common.wechat_util import get_wxuser_info, get_wxuser_list

Base = declarative_base()

PHONE_FORMAT = r'1(3|4|5|7|8)\d{9}'

API = r'http://cx.shouji.360.cn/phonearea.php?number={phone}'

# 广东的area_id直接写在这里, 不查询了
GUANGDONG_ID = 440000


def get_timestamp():
    return int(time.time())


class Area(Base):
    """
    省，市，县/区
    """
    __tablename__ = 'area'

    id = Column(Integer, primary_key=True)
    created_at = Column(Integer, nullable=False, default=get_timestamp)
    updated_at = Column(Integer, nullable=False, default=get_timestamp, onupdate=get_timestamp)
    deleted_at = Column(Integer, nullable=False, default=0)

    type = Column(Integer, nullable=False, default=0)
    name = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='',)
    pid = Column(Integer, nullable=False, default=0)


class WxUser(Base):
    __tablename__ = 'wxuser'

    id = Column(Integer, primary_key=True)
    created_at = Column(Integer, nullable=False, default=get_timestamp)
    updated_at = Column(Integer, nullable=False, default=get_timestamp, onupdate=get_timestamp)
    deleted_at = Column(Integer, nullable=False, default=0)

    subscribe = Column(Integer, nullable=False, default=0)
    openid = Column(String(64, 'utf8mb4_unicode_ci'), nullable=False, default='', index=True)
    nickname = Column(String(64, 'utf8mb4_unicode_ci'), nullable=False, default='')
    sex = Column(Integer, nullable=False, default=0)
    language = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='')
    city = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='')
    province = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='')
    country = Column(String(16, 'utf8mb4_unicode_ci'), nullable=False, default='')
    headimgurl = Column(String(256, 'utf8mb4_unicode_ci'), nullable=False, default='')
    subscribe_time = Column(Integer, nullable=False, default=0)
    remark = Column(String(128, 'utf8mb4_unicode_ci'), nullable=False, default='')
    groupid = Column(Integer, nullable=False, default=0)
    tagid_list = Column(JSON)
    area_id = Column(Integer, default=0)


class WxUserNew(Base):
    __tablename__ = 'wxuser_new'

    id = Column(Integer, primary_key=True)
    created_at = Column(Integer, nullable=False, default=get_timestamp)
    updated_at = Column(Integer, nullable=False, default=get_timestamp, onupdate=get_timestamp)
    deleted_at = Column(Integer, nullable=False, default=0)

    subscribe = Column(Integer, nullable=False, default=0)
    openid = Column(String(64, 'utf8mb4_unicode_ci'), nullable=False, default='', index=True)
    nickname = Column(String(64, 'utf8mb4_unicode_ci'), nullable=False, default='')
    sex = Column(Integer, nullable=False, default=0)
    language = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='')
    city = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='')
    province = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='')
    country = Column(String(16, 'utf8mb4_unicode_ci'), nullable=False, default='')
    headimgurl = Column(String(256, 'utf8mb4_unicode_ci'), nullable=False, default='')
    subscribe_time = Column(Integer, nullable=False, default=0)
    remark = Column(String(128, 'utf8mb4_unicode_ci'), nullable=False, default='')
    groupid = Column(Integer, nullable=False, default=0)
    tagid_list = Column(JSON)
    area_id = Column(Integer, default=0)


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()


def first_sync_wxuser():
    """
    created by chenhj at 2017/8/31
    只跑一次. 重新拉取所有微信关注用户, 为了清理脏数据
    """
    ret = get_wxuser_list()
    all_openids = ret['data']['openid']
    for openid in all_openids:
        print(openid)
        user_info = get_wxuser_info(openid)
        wxuser = WxUserNew(openid=openid)
        wxuser.subscribe = 1
        wxuser.nickname = user_info['nickname'].encode('utf8')
        wxuser.sex = user_info['sex']
        wxuser.language = user_info['language']
        wxuser.city = user_info['city']
        wxuser.province = user_info['province']
        wxuser.country = user_info['country']
        wxuser.headimgurl = user_info['headimgurl']
        wxuser.subscribe_time = user_info['subscribe_time']
        wxuser.remark = user_info['remark']
        wxuser.groupid = user_info['groupid']
        wxuser.tagid_list = user_info['tagid_list']

        # 填充扩展字段prov
        res = re.search(PHONE_FORMAT, user_info['nickname'])
        if res is None:
            # 昵称无手机, 则取微信用户的province, 若province不存在于我们系统, 则去广东
            prov = user_info['province'] if user_info['province'] else '广东'
            area = session.query(Area).filter(
                Area.name.like('{}%'.format(prov))
            ).first()
            if area:
                wxuser.area_id = area.id
            else:
                wxuser.area_id = GUANGDONG_ID
        else:
            phone = res.group()
            phone_info = json.loads(requests.get(API.format(phone=phone)).content)
            if phone_info['code'] == 0:
                prov = phone_info['data']['province']
                area = session.query(Area).filter(
                    Area.name.like('{}%'.format(prov))
                ).first()
                if area:
                    wxuser.area_id = area.id
                else:
                    prov = user_info['province'] if user_info['province'] else '广东'
                    area = session.query(Area).filter(
                        Area.name.like('{}%'.format(prov))
                    ).first()
                    if area:
                        wxuser.area_id = area.id
                    else:
                        wxuser.area_id = GUANGDONG_ID
        session.add(wxuser)
    session.commit()
    session.close()


def sync_all_wxuser():
    """
    created by chenhj at 2017/8/4
    同步微信用户的area_id字段
    """
    select_all_wxuser_sql = """
        select distinct openid from wxuser where area_id = 0 and subscribe = 1;
    """

    result = session.execute(select_all_wxuser_sql).fetchall()
    db_openids = [x.openid for x in result]
    for openid in db_openids:
        print(openid)
        user_info = get_wxuser_info(openid)
        wxuser = session.query(WxUser).filter_by(openid=openid).first()
        if not wxuser:
            continue

        wxuser.subscribe = 1
        wxuser.nickname = user_info['nickname'].encode('utf8')
        wxuser.sex = user_info['sex']
        wxuser.language = user_info['language']
        wxuser.city = user_info['city']
        wxuser.province = user_info['province']
        wxuser.country = user_info['country']
        wxuser.headimgurl = user_info['headimgurl']
        wxuser.subscribe_time = user_info['subscribe_time']
        wxuser.remark = user_info['remark']
        wxuser.groupid = user_info['groupid']
        wxuser.tagid_list = user_info['tagid_list']

        # 填充扩展字段prov
        res = re.search(PHONE_FORMAT, user_info['nickname'])
        if res is None:
            # 昵称无手机, 则取微信用户的province, 若province不存在于我们系统, 则去广东
            prov = user_info['province'] if user_info['province'] else '广东'
            area = session.query(Area).filter(
                Area.name.like('{}%'.format(prov))
            ).first()
            if area:
                wxuser.area_id = area.id
            else:
                wxuser.area_id = GUANGDONG_ID
        else:
            phone = res.group()
            phone_info = json.loads(requests.get(API.format(phone=phone)).content)
            if phone_info['code'] == 0:
                prov = phone_info['data']['province']
                area = session.query(Area).filter(
                    Area.name.like('{}%'.format(prov))
                ).first()
                if area:
                    wxuser.area_id = area.id
                else:
                    prov = user_info['province'] if user_info['province'] else '广东'
                    area = session.query(Area).filter(
                        Area.name.like('{}%'.format(prov))
                    ).first()
                    if area:
                        wxuser.area_id = area.id
                    else:
                        wxuser.area_id = GUANGDONG_ID

        session.add(wxuser)
    session.commit()
    session.close()


if __name__ == '__main__':
    first_sync_wxuser()
    # print(get_wxuser_info('o1DGe1OMj8V0lZd0ppgMfPDrE1ao'))
